## Carregar Pacotes e Conectar ao Banco de Dados 

```{r}
#| message: false
#| warning: false
library(RSQLite)
library(readr)

# Estabelece a conexão com o arquivo do banco de dados SQLite
# A conexão é armazenada na variável 'conn' para ser usada posteriormente

conn <- dbConnect(RSQLite::SQLite(), "database.sqlite3")

cat("--- Início da Análise ---\n\n")
```

## Carregar Dados dos CSVs para o Banco de Dados 

```{r}
#| message: false
#| warning: false
cat("Carregando dados dos arquivos CSV para o banco de dados...\n")

# Uma lista que mapeia o nome da tabela no banco de dados para o nome do arquivo .csv

tabelas_para_carregar <- list(
  "instructors" = "instructors.csv",
  "teachings" = "teachings.csv",
  "sections" = "sections.csv",
  "course_offerings" = "course_offerings.csv",
  "subject_memberships" = "subject_memberships.csv",
  "subjects" = "subjects.csv",
  "grade_distribution" = "grade_distributions.csv",
  "courses" = "courses.csv",
  "rooms" = "rooms.csv",
  "schedules" = "schedules.csv"
)

# Um loop 'for' que itera sobre a lista acima

for (nome_tabela in names(tabelas_para_carregar)) {
  ficheiro_csv <- tabelas_para_carregar[[nome_tabela]]
  
  dados_csv <- read_csv(ficheiro_csv)
  
  # Escreve o data frame para o banco de dados SQLite
  # 'overwrite = TRUE' : gse a tabela já existir, ela será substituída
  
  dbWriteTable(conn, nome_tabela, dados_csv, overwrite = TRUE, row.names = FALSE)
  cat(paste("Tabela '", nome_tabela, "' carregada.\n", sep=""))
}
cat("Carregamento de todas as tabelas concluído.\n\n")
```

## Quem são e quantos são os professores de Estatística? 

```{r}
#| message: false
#| warning: false
cat("PERGUNTA 1: Quantos professores lecionaram disciplinas de Estatística?\n")

# Esta é uma consulta SQL (Query) para encontrar a resposta
# 'INNER JOIN': usado para unir várias tabelas para criar uma ligação entre 'instructors' (professores) e 'subjects' (assuntos/disciplinas)
# 'COUNT(DISTINCT i.id)' é usado para contar cada professor apenas uma vez, mesmo que ele tenha lecionado várias disciplinas de estatística

query_contagem <- "
SELECT COUNT(DISTINCT i.id) AS total_professores FROM instructors i
INNER JOIN teachings t ON i.id = t.instructor_id
INNER JOIN sections s ON t.section_uuid = s.uuid
INNER JOIN course_offerings co ON s.course_offering_uuid = co.uuid
INNER JOIN subject_memberships sm ON co.uuid = sm.course_offering_uuid
INNER JOIN subjects sub ON sm.subject_code = sub.code
WHERE sub.abbreviation = 'STAT';"

# 'dbGetQuery' envia a consulta para o banco de dados e retorna o resultado

total_professores <- dbGetQuery(conn, query_contagem)

cat("RESULTADO: O número total de professores de Estatística é", total_professores$total_professores, "\n\n")
```

## Análise de GPA (Professores e Disciplinas) 

```{r}
#| message: false
#| warning: false
# Para evitar cálculos repetidos, usa-se uma Common Table Expression (CTE) com o nome 'gpa_por_oferta'
# Uma CTE é como uma tabela temporária que existe apenas durante a execução da consulta

cte_gpa_stat <- "
WITH gpa_por_oferta AS (
  SELECT
    gd.course_offering_uuid,
    -- A fórmula abaixo calcula o GPA ponderado. Cada nota (A=4, B=3, etc.)
    -- é multiplicada pelo número de alunos que a obtiveram.
    CAST(
      (gd.a_count * 4.0) + (gd.ab_count * 3.5) + (gd.b_count * 3.0) + (gd.bc_count * 2.5) +
      (gd.c_count * 2.0) + (gd.d_count * 1.0) + (gd.f_count * 0.0)
    AS REAL) /
    -- O resultado é dividido pelo número total de alunos para obter a média.
    -- A condição 'WHERE' no final garante que não haja divisão por zero.
    (gd.a_count + gd.ab_count + gd.b_count + gd.bc_count + gd.c_count + gd.d_count + gd.f_count) AS gpa
  FROM grade_distribution gd
  INNER JOIN subject_memberships sm ON gd.course_offering_uuid = sm.course_offering_uuid
  INNER JOIN subjects s ON sm.subject_code = s.code
  WHERE s.abbreviation = 'STAT' AND
        (gd.a_count + gd.ab_count + gd.b_count + gd.bc_count + gd.c_count + gd.d_count + gd.f_count) > 0
)
"
```

## Ranking dos professores mais difíceis (menor GPA)

```{r}
#| message: false
#| warning: false
cat("PERGUNTA 2a: Ranking dos professores (do mais difícil para o mais fácil)\n")

# A consulta agrupa os dados por professor, calcula a média de GPA de todas as turmas que ele lecionou e ordena o resultado em ordem crescente ('ASC') de GPA

query_prof_dificil <- paste(cte_gpa_stat, "
SELECT i.name, AVG(gpo.gpa) AS media_gpa FROM gpa_por_oferta gpo
INNER JOIN sections s ON gpo.course_offering_uuid = s.course_offering_uuid
INNER JOIN teachings t ON s.uuid = t.section_uuid
INNER JOIN instructors i ON t.instructor_id = i.id
GROUP BY i.name HAVING media_gpa IS NOT NULL ORDER BY media_gpa ASC;
")

prof_dificil <- dbGetQuery(conn, query_prof_dificil)

cat("RESULTADO:\n"); print(prof_dificil)
```

## Ranking dos professores mais fáceis (maior GPA)

```{r}
#| message: false
#| warning: false
cat("\nPERGUNTA 2b: Ranking dos professores (do mais fácil para o mais difícil)\n")

query_prof_facil <- paste(cte_gpa_stat, "
SELECT i.name, AVG(gpo.gpa) AS media_gpa FROM gpa_por_oferta gpo
INNER JOIN sections s ON gpo.course_offering_uuid = s.course_offering_uuid
INNER JOIN teachings t ON s.uuid = t.section_uuid
INNER JOIN instructors i ON t.instructor_id = i.id
GROUP BY i.name HAVING media_gpa IS NOT NULL ORDER BY media_gpa DESC;
")

prof_facil <- dbGetQuery(conn, query_prof_facil)

cat("RESULTADO:\n"); print(prof_facil)
```

## Ranking das disciplinas mais difíceis (menor GPA)

```{r}
#| message: false
#| warning: false
cat("\nPERGUNTA 2c: Ranking das disciplinas (da mais difícil para a mais fácil)\n")

# Agrupa por disciplina (nome e número), calcula o GPA médio e ordena de forma crescente

query_disc_dificil <- paste(cte_gpa_stat, "
SELECT c.name, c.number, AVG(gpo.gpa) as media_gpa FROM gpa_por_oferta gpo
INNER JOIN course_offerings co ON gpo.course_offering_uuid = co.uuid
INNER JOIN courses c ON co.course_uuid = c.uuid
GROUP BY c.name, c.number HAVING media_gpa IS NOT NULL ORDER BY media_gpa ASC;
")

disc_dificil <- dbGetQuery(conn, query_disc_dificil)

cat("RESULTADO:\n"); print(disc_dificil)
```

## Ranking das disciplinas mais fáceis (maior GPA)

```{r}
#| message: false
#| warning: false
cat("\nPERGUNTA 2d: Ranking das disciplinas (da mais fácil para a mais difícil)\n")

# Agrupa por disciplina, calcula o GPA médio e ordena de forma decrescente

query_disc_facil <- paste(cte_gpa_stat, "
SELECT c.name, c.number, AVG(gpo.gpa) as media_gpa FROM gpa_por_oferta gpo
INNER JOIN course_offerings co ON gpo.course_offering_uuid = co.uuid
INNER JOIN courses c ON co.course_uuid = c.uuid
GROUP BY c.name, c.number HAVING media_gpa IS NOT NULL ORDER BY media_gpa DESC;
")

disc_facil <- dbGetQuery(conn, query_disc_facil)

cat("RESULTADO:\n"); print(disc_facil)
```

## Desconexão do Banco de Dados 
```{r}
#| message: false
#| warning: false
dbDisconnect(conn)
cat("\n--- Fim da Análise ---\nConexão com o banco de dados fechada.\n")
```
